<!DOCTYPE HTML>
<html lang="zh-CN">


<head>
    <meta charset="utf-8">
    <meta name="keywords" content="PostgreSQL-索引, java,nodejs">
    <meta name="description" content="Indexes 索引是一种优化数据库性能的常用方式。索引允许数据库服务器查询更快。PostgreSQL 提供 B-tree, hash, GiST, SP-GiST, GIN, 和 BRIN 索引方法。



创建索引

CREATE IN">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=no">
    <meta name="renderer" content="webkit|ie-stand|ie-comp">
    <meta name="mobile-web-app-capable" content="yes">
    <meta name="format-detection" content="telephone=no">
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
    <title>PostgreSQL-索引 | libo 工作学习记录</title>
    <link rel="icon" type="image/png" href="/favicon.png">

    <link rel="stylesheet" type="text/css" href="/libs/awesome/css/all.css">
    <link rel="stylesheet" type="text/css" href="/libs/materialize/materialize.min.css">
    <link rel="stylesheet" type="text/css" href="/libs/aos/aos.css">
    <link rel="stylesheet" type="text/css" href="/libs/animate/animate.min.css">
    <link rel="stylesheet" type="text/css" href="/libs/lightGallery/css/lightgallery.min.css">
    <link rel="stylesheet" type="text/css" href="/libs/highlight/styles/monokai.css">
    <link rel="stylesheet" type="text/css" href="/css/matery.css">
    <link rel="stylesheet" type="text/css" href="/css/my.css">
    
    <script src="/libs/jquery/jquery.min.js"></script>
    <script data-ad-client="ca-pub-6820955780229803" async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
<meta name="generator" content="Hexo 5.2.0"></head>


<body>
    <header class="navbar-fixed">
    <nav id="headNav" class="bg-color nav-transparent">
        <div id="navContainer" class="nav-wrapper head-container">
            <div class="brand-logo">
                <a href="/" class="waves-effect waves-light">
                    
                    <img src="/medias/logo.png" class="logo-img" alt="LOGO">
                    
                    <span class="logo-span">libo 工作学习记录</span>
                </a>
            </div>
            

<a href="#" data-target="mobile-nav" class="sidenav-trigger button-collapse"><i class="fas fa-bars"></i></a>
<ul class="right nav-menu">
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/" class="waves-effect waves-light">
      
      <i class="fas fa-home" style="zoom: 0.6;"></i>
      
      <span>首页</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/tags" class="waves-effect waves-light">
      
      <i class="fas fa-tags" style="zoom: 0.6;"></i>
      
      <span>标签</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/categories" class="waves-effect waves-light">
      
      <i class="fas fa-bookmark" style="zoom: 0.6;"></i>
      
      <span>分类</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/archives" class="waves-effect waves-light">
      
      <i class="fas fa-archive" style="zoom: 0.6;"></i>
      
      <span>归档</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/about" class="waves-effect waves-light">
      
      <i class="fas fa-user-circle" style="zoom: 0.6;"></i>
      
      <span>关于</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/contact" class="waves-effect waves-light">
      
      <i class="fas fa-comments" style="zoom: 0.6;"></i>
      
      <span>留言板</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/friends" class="waves-effect waves-light">
      
      <i class="fas fa-address-book" style="zoom: 0.6;"></i>
      
      <span>友情链接</span>
    </a>
    
  </li>
  
  <li>
    <a href="#searchModal" class="modal-trigger waves-effect waves-light">
      <i id="searchIcon" class="fas fa-search" title="搜索" style="zoom: 0.85;"></i>
    </a>
  </li>
</ul>

<div id="mobile-nav" class="side-nav sidenav">

    <div class="mobile-head bg-color">
        
        <img src="/medias/logo.png" class="logo-img circle responsive-img">
        
        <div class="logo-name">libo 工作学习记录</div>
        <div class="logo-desc">
            
            后台开发、编程、学习记录
            
        </div>
    </div>

    

    <ul class="menu-list mobile-menu-list">
        
        <li class="m-nav-item">
	  
		<a href="/" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-home"></i>
			
			首页
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/tags" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-tags"></i>
			
			标签
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/categories" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-bookmark"></i>
			
			分类
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/archives" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-archive"></i>
			
			归档
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/about" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-user-circle"></i>
			
			关于
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/contact" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-comments"></i>
			
			Contact
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/friends" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-address-book"></i>
			
			友情链接
		</a>
          
        </li>
        
        
    </ul>
</div>

        </div>

        
    </nav>

</header>

    



<div class="bg-cover pd-header post-cover" style="background-image: url('/images/PostgreSQL.png')">
    <div class="container" style="right: 0px;left: 0px;">
        <div class="row">
            <div class="col s12 m12 l12">
                <div class="brand">
                    <h1 class="description center-align post-title">PostgreSQL-索引</h1>
                </div>
            </div>
        </div>
    </div>
</div>




<main class="post-container content">

    
    <link rel="stylesheet" href="/libs/tocbot/tocbot.css">
<style>
    #articleContent h1::before,
    #articleContent h2::before,
    #articleContent h3::before,
    #articleContent h4::before,
    #articleContent h5::before,
    #articleContent h6::before {
        display: block;
        content: " ";
        height: 100px;
        margin-top: -100px;
        visibility: hidden;
    }

    #articleContent :focus {
        outline: none;
    }

    .toc-fixed {
        position: fixed;
        top: 64px;
    }

    .toc-widget {
        width: 345px;
        padding-left: 20px;
    }

    .toc-widget .toc-title {
        margin: 35px 0 15px 0;
        padding-left: 17px;
        font-size: 1.5rem;
        font-weight: bold;
        line-height: 1.5rem;
    }

    .toc-widget ol {
        padding: 0;
        list-style: none;
    }

    #toc-content {
        height: calc(100vh - 250px);
        overflow: auto;
    }

    #toc-content ol {
        padding-left: 10px;
    }

    #toc-content ol li {
        padding-left: 10px;
    }

    #toc-content .toc-link:hover {
        color: #42b983;
        font-weight: 700;
        text-decoration: underline;
    }

    #toc-content .toc-link::before {
        background-color: transparent;
        max-height: 25px;
    }

    #toc-content .is-active-link {
        color: #42b983;
    }

    #toc-content .is-active-link::before {
        background-color: #42b983;
    }

    #floating-toc-btn {
        position: fixed;
        right: 15px;
        bottom: 76px;
        padding-top: 15px;
        margin-bottom: 0;
        z-index: 998;
    }

    #floating-toc-btn .btn-floating {
        width: 48px;
        height: 48px;
    }

    #floating-toc-btn .btn-floating i {
        line-height: 48px;
        font-size: 1.4rem;
    }
</style>
<div class="row">
    <div id="main-content" class="col s12 m12 l9">
        <!-- 文章内容详情 -->
<div id="artDetail">
    <div class="card">
        <div class="card-content article-info">
            <div class="row tag-cate">
                <div class="col s7">
                    
                    <div class="article-tag">
                        
                            <a href="/tags/%E7%B4%A2%E5%BC%95/">
                                <span class="chip bg-color">索引</span>
                            </a>
                        
                            <a href="/tags/PostgreSQL/">
                                <span class="chip bg-color">PostgreSQL</span>
                            </a>
                        
                    </div>
                    
                </div>
                <div class="col s5 right-align">
                    
                    <div class="post-cate">
                        <i class="fas fa-bookmark fa-fw icon-category"></i>
                        
                            <a href="/categories/PostgreSQL/" class="post-category">
                                PostgreSQL
                            </a>
                        
                            <a href="/categories/PostgreSQL/%E7%B4%A2%E5%BC%95/" class="post-category">
                                索引
                            </a>
                        
                    </div>
                    
                </div>
            </div>

            <div class="post-info">
                
                <div class="post-date info-break-policy">
                    <i class="far fa-calendar-minus fa-fw"></i>发布日期:&nbsp;&nbsp;
                    2020-03-05
                </div>
                

                
                <div class="post-date info-break-policy">
                    <i class="far fa-calendar-check fa-fw"></i>更新日期:&nbsp;&nbsp;
                    2020-03-05
                </div>
                

                
                <div class="info-break-policy">
                    <i class="far fa-file-word fa-fw"></i>文章字数:&nbsp;&nbsp;
                    1.9k
                </div>
                

                
                <div class="info-break-policy">
                    <i class="far fa-clock fa-fw"></i>阅读时长:&nbsp;&nbsp;
                    7 分
                </div>
                
				
                
                    <div id="busuanzi_container_page_pv" class="info-break-policy">
                        <i class="far fa-eye fa-fw"></i>阅读次数:&nbsp;&nbsp;
                        <span id="busuanzi_value_page_pv"></span>
                    </div>
				
            </div>
            
        </div>
        <hr class="clearfix">
        <div class="card-content article-card-content">
            <div id="articleContent">
                <p>Indexes 索引是一种优化数据库性能的常用方式。索引允许数据库服务器查询更快。PostgreSQL 提供 B-tree, hash, GiST, SP-GiST, GIN, 和 BRIN 索引方法。</p>
<a id="more"></a>

<ol>
<li>创建索引</li>
</ol>
<figure class="highlight sql"><table><tr><td class="code"><pre><code class="hljs SQL"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> test1_id_index <span class="hljs-keyword">ON</span> test1 (<span class="hljs-keyword">id</span>);<br></code></pre></td></tr></table></figure>

<h2 id="索引简述"><a href="#索引简述" class="headerlink" title="索引简述"></a>索引简述</h2><p>一旦索引创建，无须干涉：系统会自动在表更新的时候更新索引。在一个大表上建立索引很花时间。</p>
<p>在索引创建后，系统会让它与表保持同步。这对数据操作添加了工作量。因此，几乎不用或很少使用的索引应该删除。</p>
<h2 id="PostgreSQL-索引简述"><a href="#PostgreSQL-索引简述" class="headerlink" title="PostgreSQL 索引简述"></a>PostgreSQL 索引简述</h2><p>PostgreSQL 提供了集中索引类型，B-tree， Hash， GiST，SP-GiST， GIN， 和 BRIN。每种索引类型使用不同的算法，最适合特定类型的查询。默认，<code>CREATE INDEX</code> 创建 B-tree 索引，使用与大多数情况。</p>
<p>B-trees 索引支持在能够排序的数据上处理相等或范围查询。尤其，PostgreSQL query planner 会考虑使用 B-tree 索引，当以下操作符被使用时：&lt;, &lt;=, =, &gt;=, &gt;。</p>
<p>Hash 索引只能处理简单的相等比较。接下来创建一个 Hash 索引：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><code class="hljs SQL"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">ON</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">USING</span> <span class="hljs-keyword">HASH</span> (<span class="hljs-keyword">column</span>);<br></code></pre></td></tr></table></figure>

<p>Gist 索引不是一种索引，而是很多不同索引策略的基础。PostgreSQL 的标准发行版包含二维地理数据类型的操作符支持：<code>&lt;&lt;, &amp;&lt;, &amp;&gt;, &gt;&gt;, &lt;&lt;, &amp;&lt;, |&amp;&gt;, |&gt;&gt;, @&gt;, &lt;@, ~=, &amp;&amp;</code>。</p>
<p>GiST 索引同时能够优化“近邻”搜索，例如：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><code class="hljs SQL"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> places <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> locatin &lt;-&gt; point <span class="hljs-string">&#x27;(101, 456)&#x27;</span> <span class="hljs-keyword">LIMIT</span> <span class="hljs-number">10</span>;<br></code></pre></td></tr></table></figure>

<p>SP-GiST 索引，像 GiST 索引，提供了基础设施来支持各种搜索。SP-GiST 允许实现大范围的不同的不平衡的基于设备的数据结构，例如 quadtrees， k-d trees， radix trees。标准 PostgreSQL 发行版包括二维点的 SP-GiST 操作符，这些操作符支持索引查询：&lt;&lt;, &gt;&gt;, ~=, &lt;@, &lt;^, &gt;^。</p>
<p>GIN 索引，像反转索引，合适于包含多个组件值，例如数组。一个反转索引为每个组件值包含了分离的入口，能够有效地处理“测试特定组件值存在性”的查询。</p>
<p>像 GiST 和 SP-GiST，GIN 能够支持很多不同的用户定义索引策略，GIN 索引能够使用的操作符取决于索引策略。作为示例，PostgreSQL 标准发行版包含一个数组的 GIN 操作符类，支持下列操作符索引查询：<code>&lt;@, @&gt;, =, &amp;&amp;</code>。</p>
<p>BRIN (Block Range Indexes) 索引，存储一个表中连续物理块的总结。</p>
<h2 id="多列索引"><a href="#多列索引" class="headerlink" title="多列索引"></a>多列索引</h2><figure class="highlight sql"><table><tr><td class="code"><pre><code class="hljs SQL">        <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> test2 (<br>          major <span class="hljs-built_in">int</span>,<br>          minor <span class="hljs-built_in">int</span>,<br>          <span class="hljs-keyword">name</span> <span class="hljs-built_in">varchar</span><br>);<br></code></pre></td></tr></table></figure>

<p>查询</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><code class="hljs SQL"><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">FROM</span> test2 <span class="hljs-keyword">WHERE</span> major = <span class="hljs-keyword">constant</span> <span class="hljs-keyword">AND</span> minor = <span class="hljs-keyword">constant</span>;<br></code></pre></td></tr></table></figure>

<p>创建索引</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><code class="hljs SQL"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> test2_mm_idx <span class="hljs-keyword">ON</span> test2 (major, minor);<br></code></pre></td></tr></table></figure>

<p>目前，只有 B-tree， GiST，GIN， BRIN 支持多列索引，最多 32 列。</p>
<p>一个多列 B-tree 索引可以用来查询包含索引列的任意子集。</p>
<h2 id="索引和-ORDER-BY"><a href="#索引和-ORDER-BY" class="headerlink" title="索引和 ORDER BY"></a>索引和 ORDER BY</h2><p>索引除了简化查询，同样能够按特定顺序输出。这允许 ORDER BY 启用而不用单独的排序步骤。PostgreSQL 当前仅支持 B-tree 有序输出。</p>
<p>planner 考虑满足  <code>ORDER BY</code>，要么通过扫描一个索引，要么扫描表的物理顺序，然后另外做一个排序步骤。对于一个大分数表的查询，一个具体的排序比使用索引更快，因为它去要更少的磁盘 IO，因为跟随了顺序进入模式。但当只需要获取少量行的时候，索引更有用。一个重要的特别的例子是 <code>ORDER BY</code> 与 <code>LIMIT n</code> 的结合：为了获得前 n 条数据，必须处理所有的数据，但是如果有一个索引匹配 <code>ORDER BY</code>, 前 n 行可能直接提取，而不用扫描剩下的。</p>
<p>默认， B-tree 索引升序存储 entries，null 最后。但是，可以在创建索引的时候修改。例如：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><code class="hljs SQL"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> test2_info_nulls_low <span class="hljs-keyword">ON</span> test2 (info <span class="hljs-keyword">NULLS</span> <span class="hljs-keyword">FIRST</span>);<br> <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> test3_desc_index <span class="hljs-keyword">ON</span> test3 (<span class="hljs-keyword">id</span> <span class="hljs-keyword">DESC</span> <span class="hljs-keyword">NULLS</span> <span class="hljs-keyword">LAST</span>);<br></code></pre></td></tr></table></figure>

<h2 id="表达式索引"><a href="#表达式索引" class="headerlink" title="表达式索引"></a>表达式索引</h2><p>一个索引列不仅仅可以是表的一个列，还可以是一个一列或多列组成的表达式。这个特点在需要快速获取计算结果是特别有用。</p>
<p>例如：使用 lower 函数的大小写不敏感的比较：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><code class="hljs SQL"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> test1 <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">lower</span>(col1) = <span class="hljs-string">&#x27;value&#x27;</span>;<br></code></pre></td></tr></table></figure>

<p>这个查询可以用下列的索引：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><code class="hljs SQL"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> test1_lower_col1_idx <span class="hljs-keyword">ON</span> test1 (<span class="hljs-keyword">lower</span>(col1));<br></code></pre></td></tr></table></figure>

<p>索引表达式维护起来代价很大，因为每行插入或更新的时候都要计算表达式。因此，当只有提取速度比插入或更新更重要时，对表达式的索引才有用。</p>
<h2 id="Partial-Indexes（部分索引）"><a href="#Partial-Indexes（部分索引）" class="headerlink" title="Partial Indexes（部分索引）"></a>Partial Indexes（部分索引）</h2><p>部分索引是构建在表上的一个子集的索引；子集由状态表达式定义。索引只会包含满足条件的行。部分索引是一个独特的特点，因为在几种条件下非常有用。</p>
<p>一个使用部分索引的主要原因是避免索引“常见值”。因为不管怎样，“常见值” 的查询不会使用索引，保存这些行的索引没有意义。这减少了索引的大小，加速了这些查询。它也同样会加速很多更新操作的查询，因为不是所有情况都需要更新索引。</p>
<p>创建索引使用如下类似命令：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><code class="hljs SQL"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> access_log_client_ip_ix <span class="hljs-keyword">ON</span> access_log (client_ip) <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">NOT</span> (client_ip &gt; inet <span class="hljs-string">&#x27;192.168.100.0&#x27;</span> <span class="hljs-keyword">AND</span><br>client_ip &lt; inet <span class="hljs-string">&#x27;192.168.100.255&#x27;</span>);<br></code></pre></td></tr></table></figure>

<p>一个会使用这个索引的查询：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><code class="hljs SQL"><span class="hljs-keyword">SELECT</span> *<br><span class="hljs-keyword">FROM</span> access_log<br><span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">url</span> = <span class="hljs-string">&#x27;/index.html&#x27;</span> <span class="hljs-keyword">AND</span> client_ip = inet <span class="hljs-string">&#x27;212.78.10.32&#x27;</span>;<br></code></pre></td></tr></table></figure>

<p>一个不会使用这个索引的例子：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><code class="hljs SQL"><span class="hljs-keyword">SELECT</span> *<br><span class="hljs-keyword">FROM</span> access_log<br><span class="hljs-keyword">WHERE</span> client_ip = inet <span class="hljs-string">&#x27;192.168.100.23&#x27;</span>;<br></code></pre></td></tr></table></figure>

<h3 id="建立索引排除不感兴趣的值"><a href="#建立索引排除不感兴趣的值" class="headerlink" title="建立索引排除不感兴趣的值"></a>建立索引排除不感兴趣的值</h3><figure class="highlight sql"><table><tr><td class="code"><pre><code class="hljs SQL"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> orders_unbilled_index <span class="hljs-keyword">ON</span> orders (order_nr) <span class="hljs-keyword">WHERE</span> billed <span class="hljs-keyword">is</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">true</span>;<br></code></pre></td></tr></table></figure>

<p>以下 SQL 查询能够用到以上索引：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><code class="hljs SQL"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> orders <span class="hljs-keyword">WHERE</span> billed <span class="hljs-keyword">is</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">true</span> <span class="hljs-keyword">AND</span> order_nr &lt; <span class="hljs-number">10000</span>;<br><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> orders <span class="hljs-keyword">WHERE</span> billed <span class="hljs-keyword">is</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">true</span> <span class="hljs-keyword">AND</span> amount &gt; <span class="hljs-number">5000.00</span>;<br></code></pre></td></tr></table></figure>

<h2 id="Operator-Classes-和-Operator-Families"><a href="#Operator-Classes-和-Operator-Families" class="headerlink" title="Operator Classes 和 Operator Families"></a>Operator Classes 和 Operator Families</h2><p>一个索引定义可以为每个索引列定义一个 operator class。</p>
<p>操作符类 text_pattern_ops, varchar_pattern_ops, 和 bpchar_pattern_ops 在 B-tree 索引上相应的支持 text, varchar, char。</p>
<p>可以定义自己的操作符类。</p>
<p>如果使用 C locale，默认的操作符类就足够了。</p>
<h2 id="Index-Only-Scans"><a href="#Index-Only-Scans" class="headerlink" title="Index-Only Scans"></a>Index-Only Scans</h2><p>PostgreSQL 中所有的索引都是次要索引，意味着每个索引存储与表的主数据区（表堆）分离。这意味着，一个寻常的索引扫描，每一行数据的提取需要从索引和堆中获取。而且，索引扫描进入堆是随机进入的，会很慢，尤其在传统的旋转式介质上。bitmap 扫描试图减轻这个花费通过有序进入，但是远远不能解决问题。</p>
<p>为了解决这个问题，PostgreSQL 支持仅索引扫描。基础想法是只从索引直接获取值，而不去调用堆入口。然而，有两个基础的限制：</p>
<ol>
<li><p>索引类型必须支持仅索引扫描。B-tree 总是支持的。GiST 和 SP-GiTST 索引仅支持部分操作符类的仅索引所描，其他的索引类型不支持。底层需要索引必须是物理存储的，或者能够从每个索引入口的原始数据值重构的。因此，GIN 索引不支持仅索引扫描，因为每个索引入口只保存部分原始数据值。</p>
</li>
<li><p>查询只能应用存储在索引中的列。</p>
</li>
<li><p>表的堆页所有的 visible map bits 必须设置。</p>
</li>
</ol>
<p>参考 PostgreSQL 10.10 Documentation Chapter 11.</p>

            </div>
            <hr/>

            

    <div class="reprint" id="reprint-statement">
        
            <div class="reprint__author">
                <span class="reprint-meta" style="font-weight: bold;">
                    <i class="fas fa-user">
                        文章作者:
                    </i>
                </span>
                <span class="reprint-info">
                    <a href="http://libo0.gitee.io" rel="external nofollow noreferrer">libo</a>
                </span>
            </div>
            <div class="reprint__type">
                <span class="reprint-meta" style="font-weight: bold;">
                    <i class="fas fa-link">
                        文章链接:
                    </i>
                </span>
                <span class="reprint-info">
                    <a href="http://libo0.gitee.io/2020/03/05/PostgreSQL-%E7%B4%A2%E5%BC%95/">http://libo0.gitee.io/2020/03/05/PostgreSQL-%E7%B4%A2%E5%BC%95/</a>
                </span>
            </div>
            <div class="reprint__notice">
                <span class="reprint-meta" style="font-weight: bold;">
                    <i class="fas fa-copyright">
                        版权声明:
                    </i>
                </span>
                <span class="reprint-info">
                    本博客所有文章除特別声明外，均采用
                    <a href="https://creativecommons.org/licenses/by/4.0/deed.zh" rel="external nofollow noreferrer" target="_blank">CC BY 4.0</a>
                    许可协议。转载请注明来源
                    <a href="http://libo0.gitee.io" target="_blank">libo</a>
                    !
                </span>
            </div>
        
    </div>

    <script async defer>
      document.addEventListener("copy", function (e) {
        let toastHTML = '<span>复制成功，请遵循本文的转载规则</span><button class="btn-flat toast-action" onclick="navToReprintStatement()" style="font-size: smaller">查看</a>';
        M.toast({html: toastHTML})
      });

      function navToReprintStatement() {
        $("html, body").animate({scrollTop: $("#reprint-statement").offset().top - 80}, 800);
      }
    </script>



            <div class="tag_share" style="display: block;">
                <div class="post-meta__tag-list" style="display: inline-block;">
                    
                        <div class="article-tag">
                            
                                <a href="/tags/%E7%B4%A2%E5%BC%95/">
                                    <span class="chip bg-color">索引</span>
                                </a>
                            
                                <a href="/tags/PostgreSQL/">
                                    <span class="chip bg-color">PostgreSQL</span>
                                </a>
                            
                        </div>
                    
                </div>
                <div class="post_share" style="zoom: 80%; width: fit-content; display: inline-block; float: right; margin: -0.15rem 0;">
                    <link rel="stylesheet" type="text/css" href="/libs/share/css/share.min.css">

<div id="article-share">
    
    
    <div class="social-share" data-sites="qq,qzone,wechat,weibo,douban,linkedin,twitter,facebook,google" data-wechat-qrcode-helper="<p>微信扫一扫即可分享！</p>"></div>
    <script src="/libs/share/js/social-share.min.js"></script>
    

    

</div>

                </div>
            </div>
            
                <style>
    #reward {
        margin: 40px 0;
        text-align: center;
    }

    #reward .reward-link {
        font-size: 1.4rem;
        line-height: 38px;
    }

    #reward .btn-floating:hover {
        box-shadow: 0 6px 12px rgba(0, 0, 0, 0.2), 0 5px 15px rgba(0, 0, 0, 0.2);
    }

    #rewardModal {
        width: 320px;
        height: 350px;
    }

    #rewardModal .reward-title {
        margin: 15px auto;
        padding-bottom: 5px;
    }

    #rewardModal .modal-content {
        padding: 10px;
    }

    #rewardModal .close {
        position: absolute;
        right: 15px;
        top: 15px;
        color: rgba(0, 0, 0, 0.5);
        font-size: 1.3rem;
        line-height: 20px;
        cursor: pointer;
    }

    #rewardModal .close:hover {
        color: #ef5350;
        transform: scale(1.3);
        -moz-transform:scale(1.3);
        -webkit-transform:scale(1.3);
        -o-transform:scale(1.3);
    }

    #rewardModal .reward-tabs {
        margin: 0 auto;
        width: 210px;
    }

    .reward-tabs .tabs {
        height: 38px;
        margin: 10px auto;
        padding-left: 0;
    }

    .reward-content ul {
        padding-left: 0 !important;
    }

    .reward-tabs .tabs .tab {
        height: 38px;
        line-height: 38px;
    }

    .reward-tabs .tab a {
        color: #fff;
        background-color: #ccc;
    }

    .reward-tabs .tab a:hover {
        background-color: #ccc;
        color: #fff;
    }

    .reward-tabs .wechat-tab .active {
        color: #fff !important;
        background-color: #22AB38 !important;
    }

    .reward-tabs .alipay-tab .active {
        color: #fff !important;
        background-color: #019FE8 !important;
    }

    .reward-tabs .reward-img {
        width: 210px;
        height: 210px;
    }
</style>

<div id="reward">
    <a href="#rewardModal" class="reward-link modal-trigger btn-floating btn-medium waves-effect waves-light red">赏</a>

    <!-- Modal Structure -->
    <div id="rewardModal" class="modal">
        <div class="modal-content">
            <a class="close modal-close"><i class="fas fa-times"></i></a>
            <h4 class="reward-title">你的赏识是我前进的动力</h4>
            <div class="reward-content">
                <div class="reward-tabs">
                    <ul class="tabs row">
                        <li class="tab col s6 alipay-tab waves-effect waves-light"><a href="#alipay">支付宝</a></li>
                        <li class="tab col s6 wechat-tab waves-effect waves-light"><a href="#wechat">微 信</a></li>
                    </ul>
                    <div id="alipay">
                        <img src="/medias/reward/alipay.jpg" class="reward-img" alt="支付宝打赏二维码">
                    </div>
                    <div id="wechat">
                        <img src="/medias/reward/wechat.png" class="reward-img" alt="微信打赏二维码">
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<script>
    $(function () {
        $('.tabs').tabs();
    });
</script>
            
        </div>
    </div>

    
        <link rel="stylesheet" href="/libs/gitalk/gitalk.css">
<link rel="stylesheet" href="/css/my-gitalk.css">

<div class="card gitalk-card" data-aos="fade-up">
    <div class="comment_headling" style="font-size: 20px; font-weight: 700; position: relative; left: 20px; top: 15px; padding-bottom: 5px;">
        <i class="fas fa-comments fa-fw" aria-hidden="true"></i>
        <span>评论</span>
    </div>
    <div id="gitalk-container" class="card-content"></div>
</div>

<script src="/libs/gitalk/gitalk.min.js"></script>
<script>
    let gitalk = new Gitalk({
        clientID: '',
        clientSecret: '',
        repo: '',
        owner: '',
        admin: null,
        id: '2020-03-05T14-06-27',
        distractionFreeMode: false  // Facebook-like distraction free mode
    });

    gitalk.render('gitalk-container');
</script>
    

    

    

    

    

    

<article id="prenext-posts" class="prev-next articles">
    <div class="row article-row">
        
        <div class="article col s12 m6" data-aos="fade-up">
            <div class="article-badge left-badge text-color">
                <i class="fas fa-chevron-left"></i>&nbsp;上一篇</div>
            <div class="card">
                <a href="/2020/03/06/Psql-%E7%AE%80%E5%8D%95%E4%BD%BF%E7%94%A8/">
                    <div class="card-image">
                        
                        <img src="/images/PostgreSQL.png" class="responsive-img" alt="Psql-简单使用">
                        
                        <span class="card-title">Psql-简单使用</span>
                    </div>
                </a>
                <div class="card-content article-content">
                    <div class="summary block-with-text">
                        
                            PostgreSQL  的 Psql 交互式客户端的简单使用。


psql 命令
帮助 help
SQL 命令帮助 \h
psql 命令帮助 \?
查询 \g
退出 \q
数据库列表 \l
schema 列表 \dn 
角色列表 \du

                        
                    </div>
                    <div class="publish-info">
                        <span class="publish-date">
                            <i class="far fa-clock fa-fw icon-date"></i>2020-03-06
                        </span>
                        <span class="publish-author">
                            
                            <i class="fas fa-bookmark fa-fw icon-category"></i>
                            
                            <a href="/categories/PostgreSQL/" class="post-category">
                                    PostgreSQL
                                </a>
                            
                            <a href="/categories/PostgreSQL/psql/" class="post-category">
                                    psql
                                </a>
                            
                            
                        </span>
                    </div>
                </div>
                
                <div class="card-action article-tags">
                    
                    <a href="/tags/PostgreSQL/">
                        <span class="chip bg-color">PostgreSQL</span>
                    </a>
                    
                    <a href="/tags/psql/">
                        <span class="chip bg-color">psql</span>
                    </a>
                    
                </div>
                
            </div>
        </div>
        
        
        <div class="article col s12 m6" data-aos="fade-up">
            <div class="article-badge right-badge text-color">
                下一篇&nbsp;<i class="fas fa-chevron-right"></i>
            </div>
            <div class="card">
                <a href="/2020/03/04/Postman-%E6%95%B0%E6%8D%AE%E5%8F%AF%E8%A7%86%E5%8C%96/">
                    <div class="card-image">
                        
                        <img src="/images/postman-visualize.jpg" class="responsive-img" alt="Postman-数据可视化">
                        
                        <span class="card-title">Postman-数据可视化</span>
                    </div>
                </a>
                <div class="card-content article-content">
                    <div class="summary block-with-text">
                        
                            Postman 支持在 test 脚本中添加可视化的 html 代码，就像平常开发网页一样，而且可以使用 bootstrap， jQuery 相关的库。


Postman 提供了一个沙盒环境，支持在 Postman 请求的 test 脚本
                        
                    </div>
                    <div class="publish-info">
                            <span class="publish-date">
                                <i class="far fa-clock fa-fw icon-date"></i>2020-03-04
                            </span>
                        <span class="publish-author">
                            
                            <i class="fas fa-bookmark fa-fw icon-category"></i>
                            
                            <a href="/categories/Postman/" class="post-category">
                                    Postman
                                </a>
                            
                            
                        </span>
                    </div>
                </div>
                
                <div class="card-action article-tags">
                    
                    <a href="/tags/Postman/">
                        <span class="chip bg-color">Postman</span>
                    </a>
                    
                    <a href="/tags/Postman-test-script/">
                        <span class="chip bg-color">Postman-test-script</span>
                    </a>
                    
                </div>
                
            </div>
        </div>
        
    </div>
</article>

</div>



<!-- 代码块功能依赖 -->
<script type="text/javascript" src="/libs/codeBlock/codeBlockFuction.js"></script>
<script type="text/javascript" src="/libs/highlight/highlight.pack.js"></script>
<!-- 代码语言 -->

<script type="text/javascript" src="/libs/codeBlock/codeLang.js"></script>

    
<!-- 代码块复制 -->

<script type="text/javascript" src="/libs/codeBlock/codeCopy.js"></script>


<!-- 代码块收缩 -->

<script type="text/javascript" src="/libs/codeBlock/codeShrink.js"></script>


<!-- 代码块折行 -->

<style type="text/css">
code[class*="language-"], pre[class*="language-"] { white-space: pre !important; }
</style>

    </div>
    <div id="toc-aside" class="expanded col l3 hide-on-med-and-down">
        <div class="toc-widget">
            <div class="toc-title"><i class="far fa-list-alt"></i>&nbsp;&nbsp;目录</div>
            <div id="toc-content"></div>
        </div>
    </div>
</div>

<!-- TOC 悬浮按钮. -->

<div id="floating-toc-btn" class="hide-on-med-and-down">
    <a class="btn-floating btn-large bg-color">
        <i class="fas fa-list-ul"></i>
    </a>
</div>


<script src="/libs/tocbot/tocbot.min.js"></script>
<script>
    $(function () {
        tocbot.init({
            tocSelector: '#toc-content',
            contentSelector: '#articleContent',
            headingsOffset: -($(window).height() * 0.4 - 45),
            collapseDepth: Number('0'),
            headingSelector: 'h2, h3, h4'
        });

        // modify the toc link href to support Chinese.
        let i = 0;
        let tocHeading = 'toc-heading-';
        $('#toc-content a').each(function () {
            $(this).attr('href', '#' + tocHeading + (++i));
        });

        // modify the heading title id to support Chinese.
        i = 0;
        $('#articleContent').children('h2, h3, h4').each(function () {
            $(this).attr('id', tocHeading + (++i));
        });

        // Set scroll toc fixed.
        let tocHeight = parseInt($(window).height() * 0.4 - 64);
        let $tocWidget = $('.toc-widget');
        $(window).scroll(function () {
            let scroll = $(window).scrollTop();
            /* add post toc fixed. */
            if (scroll > tocHeight) {
                $tocWidget.addClass('toc-fixed');
            } else {
                $tocWidget.removeClass('toc-fixed');
            }
        });

        
        /* 修复文章卡片 div 的宽度. */
        let fixPostCardWidth = function (srcId, targetId) {
            let srcDiv = $('#' + srcId);
            if (srcDiv.length === 0) {
                return;
            }

            let w = srcDiv.width();
            if (w >= 450) {
                w = w + 21;
            } else if (w >= 350 && w < 450) {
                w = w + 18;
            } else if (w >= 300 && w < 350) {
                w = w + 16;
            } else {
                w = w + 14;
            }
            $('#' + targetId).width(w);
        };

        // 切换TOC目录展开收缩的相关操作.
        const expandedClass = 'expanded';
        let $tocAside = $('#toc-aside');
        let $mainContent = $('#main-content');
        $('#floating-toc-btn .btn-floating').click(function () {
            if ($tocAside.hasClass(expandedClass)) {
                $tocAside.removeClass(expandedClass).hide();
                $mainContent.removeClass('l9');
            } else {
                $tocAside.addClass(expandedClass).show();
                $mainContent.addClass('l9');
            }
            fixPostCardWidth('artDetail', 'prenext-posts');
        });
        
    });
</script>

    

</main>



    <footer class="page-footer bg-color">
    <div class="container row center-align" style="margin-bottom: 15px !important;">
        <div class="col s12 m8 l8 copy-right">
            Copyright&nbsp;&copy;
            <span id="year">2020</span>
            <a href="http://libo0.gitee.io" target="_blank">libo</a>
            |&nbsp;Powered by&nbsp;<a href="https://hexo.io/" target="_blank">Hexo</a>
            |&nbsp;Theme&nbsp;<a href="https://github.com/blinkfox/hexo-theme-matery" target="_blank">Matery</a>
            <br>
            
            
            
            
            
            
            <span id="busuanzi_container_site_pv">
                |&nbsp;<i class="far fa-eye"></i>&nbsp;总访问量:&nbsp;<span id="busuanzi_value_site_pv"
                    class="white-color"></span>&nbsp;次
            </span>
            
            
            <span id="busuanzi_container_site_uv">
                |&nbsp;<i class="fas fa-users"></i>&nbsp;总访问人数:&nbsp;<span id="busuanzi_value_site_uv"
                    class="white-color"></span>&nbsp;人
            </span>
            
            <br>
            
            <span id="sitetime">载入运行时间...</span>
            <script>
                function siteTime() {
                    var seconds = 1000;
                    var minutes = seconds * 60;
                    var hours = minutes * 60;
                    var days = hours * 24;
                    var years = days * 365;
                    var today = new Date();
                    var startYear = "2020";
                    var startMonth = "3";
                    var startDate = "1";
                    var startHour = "18";
                    var startMinute = "30";
                    var startSecond = "0";
                    var todayYear = today.getFullYear();
                    var todayMonth = today.getMonth() + 1;
                    var todayDate = today.getDate();
                    var todayHour = today.getHours();
                    var todayMinute = today.getMinutes();
                    var todaySecond = today.getSeconds();
                    var t1 = Date.UTC(startYear, startMonth, startDate, startHour, startMinute, startSecond);
                    var t2 = Date.UTC(todayYear, todayMonth, todayDate, todayHour, todayMinute, todaySecond);
                    var diff = t2 - t1;
                    var diffYears = Math.floor(diff / years);
                    var diffDays = Math.floor((diff / days) - diffYears * 365);
                    var diffHours = Math.floor((diff - (diffYears * 365 + diffDays) * days) / hours);
                    var diffMinutes = Math.floor((diff - (diffYears * 365 + diffDays) * days - diffHours * hours) /
                        minutes);
                    var diffSeconds = Math.floor((diff - (diffYears * 365 + diffDays) * days - diffHours * hours -
                        diffMinutes * minutes) / seconds);
                    if (startYear == todayYear) {
                        document.getElementById("year").innerHTML = todayYear;
                        document.getElementById("sitetime").innerHTML = "本站已安全运行 " + diffDays + " 天 " + diffHours +
                            " 小时 " + diffMinutes + " 分钟 " + diffSeconds + " 秒";
                    } else {
                        document.getElementById("year").innerHTML = startYear + " - " + todayYear;
                        document.getElementById("sitetime").innerHTML = "本站已安全运行 " + diffYears + " 年 " + diffDays +
                            " 天 " + diffHours + " 小时 " + diffMinutes + " 分钟 " + diffSeconds + " 秒";
                    }
                }
                setInterval(siteTime, 1000);
            </script>
            
            <br>
            
        </div>
        <div class="col s12 m4 l4 social-link social-statis">
    <a href="https://github.com/libo-0" class="tooltipped" target="_blank" data-tooltip="访问我的GitHub" data-position="top" data-delay="50">
        <i class="fab fa-github"></i>
    </a>



    <a href="mailto:libo-0@foxmail.com" class="tooltipped" target="_blank" data-tooltip="邮件联系我" data-position="top" data-delay="50">
        <i class="fas fa-envelope-open"></i>
    </a>













    <a href="/atom.xml" class="tooltipped" target="_blank" data-tooltip="RSS 订阅" data-position="top" data-delay="50">
        <i class="fas fa-rss"></i>
    </a>

</div>
    </div>
</footer>

<div class="progress-bar"></div>


    <!-- 搜索遮罩框 -->
<div id="searchModal" class="modal">
    <div class="modal-content">
        <div class="search-header">
            <span class="title"><i class="fas fa-search"></i>&nbsp;&nbsp;搜索</span>
            <input type="search" id="searchInput" name="s" placeholder="请输入搜索的关键字"
                   class="search-input">
        </div>
        <div id="searchResult"></div>
    </div>
</div>

<script src="/js/search.js"></script>
<script type="text/javascript">
$(function () {
    searchFunc("/" + "search.xml", 'searchInput', 'searchResult');
});
</script>
    <!-- 回到顶部按钮 -->
<div id="backTop" class="top-scroll">
    <a class="btn-floating btn-large waves-effect waves-light" href="#!">
        <i class="fas fa-arrow-up"></i>
    </a>
</div>


    <script src="/libs/materialize/materialize.min.js"></script>
    <script src="/libs/masonry/masonry.pkgd.min.js"></script>
    <script src="/libs/aos/aos.js"></script>
    <script src="/libs/scrollprogress/scrollProgress.min.js"></script>
    <script src="/libs/lightGallery/js/lightgallery-all.min.js"></script>
    <script src="/js/matery.js"></script>

    <!-- Global site tag (gtag.js) - Google Analytics -->


    <!-- Baidu Analytics -->

    <!-- Baidu Push -->

<script>
    (function () {
        var bp = document.createElement('script');
        var curProtocol = window.location.protocol.split(':')[0];
        if (curProtocol === 'https') {
            bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';
        } else {
            bp.src = 'http://push.zhanzhang.baidu.com/push.js';
        }
        var s = document.getElementsByTagName("script")[0];
        s.parentNode.insertBefore(bp, s);
    })();
</script>

    
    <script src="/libs/others/clicklove.js" async="async"></script>
    
    
    <script async src="/libs/others/busuanzi.pure.mini.js"></script>
    

    

    

    

    

    
    
    
    <script src="/libs/instantpage/instantpage.js" type="module"></script>
    

</body>

</html>
